package org.example.springboot.mapper;

import org.apache.ibatis.annotations.*;
import org.example.springboot.entity.Teacher;
import org.example.springboot.entity.TeacherAndCourse;

import java.util.List;
import java.util.Map;

@Mapper
public interface TeacherMapper {

    @Select("SELECT c.course, COUNT(t.account) AS teacher_count " +
            "FROM classification c " +
            "LEFT JOIN teach t ON c.course = t.course " +
            "GROUP BY c.course")
    List<Map<String, Object>> getTeacherCourseDistribution();

    @Select("SELECT * FROM teacher WHERE username LIKE CONCAT ('%', #{username}, '%') LIMIT #{offset}, #{pageSize} ")
    List<Teacher> getTeachersByPage(@Param("pageSize") int pageSize, @Param("offset") int offset, @Param("username") String username);

    @Select("SELECT COUNT(*) FROM teacher WHERE username LIKE CONCAT ('%', #{username}, '%')")
    Integer getTeacherCount(@Param("username") String username);

    @Delete("DELETE FROM teacher WHERE account = #{account}")
    void deleteTeacherByAccount(@Param("account") String account);

    @Insert("INSERT INTO teacher (account, username, name, sex, background, speciality, age, introduction, phone_number, city, avatar) " +
            "VALUES (#{account}, #{username}, #{name}, #{sex}, #{background}, #{speciality}, #{age}, #{introduction}, #{phone_number}, #{city}, #{avatar})")
    void insertTeacher(Teacher teacher);

    @Select("SELECT * FROM teacher WHERE account = #{account}")
    Teacher selectByAccount(String account);

    @Update("UPDATE teacher SET username = #{username}, name = #{name}, sex = #{sex}, background = #{background}," +
            " speciality=#{speciality}, age=#{age}, introduction=#{introduction}," +
            "phone_number = #{phone_number}, city = #{city}, avatar = #{avatar} WHERE account = #{account}")
    void updateTeacher(Teacher teacher);

    @Select("SELECT t.account, t.name, t.speciality, t.background, t.introduction, t.avatar " +
            "FROM teacher t " +
            "JOIN teach te ON t.account = te.account " +
            "WHERE te.course = #{course}")
    List<TeacherAndCourse> findTeachersByCourse(@Param("course") String course);

    @Select("SELECT t.account, t.name, t.sex, t.background, t.speciality, t.age, " +
            "t.phone_number, t.city, t.introduction, t.avatar, te.course, te.teach_method, " +
            "te.time, te.teach_situation, te.salary " +
            "FROM teacher t " +
            "JOIN teach te ON t.account = te.account " +
            "WHERE t.account = #{account} AND te.course = #{course}")
    TeacherAndCourse selectTeacherDetail(@Param("account") String account, @Param("course") String course);

    @Update("UPDATE teacher SET password = #{password} WHERE account = #{account}")
    void updatePassword(String account, String password);
}
